{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e38baddd",
   "metadata": {},
   "source": [
    "# Pandas\n",
    "## Basics\n",
    "### Series\n",
    "1. pd.Series(data=, index=) 或者是通过python dict进行传入\n",
    "2. 能够通过index 或者是相应的位置进行索引\n",
    "3. values属性能够获取所有值，index能够获取所有索引\n",
    "4. name属性是Series的名称；index.name是索引的名称"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "94ac9a29",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    0\n",
       "b    2\n",
       "c    4\n",
       "d    6\n",
       "e    8\n",
       "dtype: int64"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd \n",
    "import numpy as np\n",
    "from pandas import DataFrame, Series\n",
    "\n",
    "s1 = pd.Series(np.arange(0,10,2),index=('a','b','c','d','e'))\n",
    "s1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "7500a735",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1['c']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "1aa8d039",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "719cc512",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['a', 'b', 'c', 'd', 'e'], dtype='object')"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "c22c697a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "alphabet\n",
       "a    0\n",
       "b    2\n",
       "c    4\n",
       "d    6\n",
       "e    8\n",
       "Name: random nums, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "s1.name = \"random nums\"\n",
    "s1.index.name = \"alphabet\"\n",
    "s1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f763aa6c",
   "metadata": {},
   "source": [
    "### DataFrame\n",
    "1. pd.DataFrame(data,columns=,index=) 或者 通过pytho dict进行传入，每一列是一个python list\n",
    "2. 建立DataFrame的时候如果有的列不存在数据，会留下空洞 因此还用使用 nest dict 或者是 Series 建立DataFrame的方法\n",
    "2. head() 方法返回表格前几行；\n",
    "3. 对已有表格使用pd.DataFrame(data, columns=[]) 可以按顺序显示所有列\n",
    "4. 从DataFrame中获取Series： 使用[]索引 或者直接以属性的方式获取列名称\n",
    "5. index 和 columns都有name属性；DataFrame每一列都是一个 Series，列名称就是Series的name属性\n",
    "6. values会返回一个二维的ndarray，dtype会返回一个最大的父类\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "539f1fad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>state</th>\n",
       "      <th>price</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2008</td>\n",
       "      <td>Ohio</td>\n",
       "      <td>200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009</td>\n",
       "      <td>California</td>\n",
       "      <td>500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2010</td>\n",
       "      <td>Washington</td>\n",
       "      <td>340</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2011</td>\n",
       "      <td>Nevada</td>\n",
       "      <td>230</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2012</td>\n",
       "      <td>Oregon</td>\n",
       "      <td>900</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year       state  price\n",
       "0  2008        Ohio    200\n",
       "1  2009  California    500\n",
       "2  2010  Washington    340\n",
       "3  2011      Nevada    230\n",
       "4  2012      Oregon    900"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pandas import DataFrame\n",
    "import numpy as np\n",
    "\n",
    "samp_dict = {\n",
    "    'year': [2008, 2009, 2010, 2011, 2012],\n",
    "    'state': [\"Ohio\",\"California\",\"Washington\", \"Nevada\",\"Oregon\"],\n",
    "    'price':[200,500,340,230,900]\n",
    "}\n",
    "samp = pd.DataFrame(samp_dict)\n",
    "samp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d35f7137",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    2008\n",
       "1    2009\n",
       "2    2010\n",
       "3    2011\n",
       "4    2012\n",
       "Name: year, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "samp.year"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "804a9495",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0          Ohio\n",
       "1    California\n",
       "2    Washington\n",
       "3        Nevada\n",
       "4        Oregon\n",
       "Name: state, dtype: object"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "samp[\"state\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "585009fc",
   "metadata": {},
   "source": [
    "#### index\n",
    "1. index以及columns是一个set，但是能够接受重复的列名称\n",
    "2. index可以进行集合的交并补操作，同时能够添加、插入、删除，没有的数据将会以空位的形式出现\n",
    "3. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dca7b10d",
   "metadata": {},
   "source": [
    "### commonly used methods\n",
    "1. reindex(index, columns,method='ffill|bfill', fill_value, limit, tolerance ) 重新定义行标，没有数据的位置将会留下空洞\n",
    "2. drop() 如果是删除行，直接输入行名称或行名称list即可；如果是删除列，那么需要axis=1或者是axis='column'；另外删除是返回一个新的对象，如果直接修改原对象，<font color=red>inplace=True</font>\n",
    "3. Series 优先选取行，DataFrame优先选取列，支持通过名称、位置进行选取，支持分块选取（块是一个完全的闭区间），同时也支持布尔选取\n",
    "4. DataFrame选取行的方法：loc-通过index名称选取 iloc-通过行的逻辑位置选取；loc 和 iloc都能够通过传入第二个位置参数来确定选取哪一列\n",
    "5. "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "235ef811",
   "metadata": {},
   "source": [
    "#### arithmetics\n",
    "1. 如果使用普通的运算符号，那么不存在的行或者列会留下空洞$(NaN)$\n",
    "2. 需要进行填充，需要使用内置函数 add(fill_value) sub(fill_value) div(fill_value) mul(fill_value) pow(fill_value) floordiv(fill_value) 前面加上r则是交换操作数的位置顺序\n",
    "3. 同样，DataFrame和Series进行计算的时候，遵循Numpy的广播法则，行计算可以直接广播，但是列计算需要使用内置函数，添加axis='index'或axis=0 参数进行规约;\n",
    "4. 如果DataFrame与Series进行计算时有的行或者列不存在，pandas会首先进行reindex操作\n",
    "5. DataFrame接收numpy ufunc函数，作用对象是全体数据np.abs np.sqrt ...\n",
    "6. DataFrame可以使用apply()函数接收自定义聚合函数，作用于一整列（也就是一个独立的Series，默认）或者是一整行（使用axis=columns 或axis=1进行规约)\n",
    "7. applymap()可以将自定义函数作用于全体数据，本质上是在作用到Series后Series本身自带一个map函数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "21a1e650",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "b    0\n",
      "d    1\n",
      "e    2\n",
      "Name: Utah, dtype: int64\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Utah</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            b   d   e\n",
       "Utah        0   1   2\n",
       "California  3   4   5\n",
       "Ohio        6   7   8\n",
       "Texas       9  10  11"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "frame = pd.DataFrame(np.arange(12).reshape(4,3),\n",
    "                     columns=list('bde'),\n",
    "                     index=['Utah',\"California\",'Ohio','Texas'])\n",
    "series= frame.iloc[0]\n",
    "print(series)\n",
    "frame"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c69f2c17",
   "metadata": {},
   "source": [
    "#### calculation over rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "491bf769",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Utah</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            b  d  e\n",
       "Utah        0  0  0\n",
       "California  3  3  3\n",
       "Ohio        6  6  6\n",
       "Texas       9  9  9"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame - series"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "1318a772",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Utah           1\n",
       "California     4\n",
       "Ohio           7\n",
       "Texas         10\n",
       "Name: d, dtype: int64"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "series = frame.iloc[:,1]\n",
    "series"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4ceafa99",
   "metadata": {},
   "source": [
    "#### calculation over columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f72f9a80",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>b</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Utah</th>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>California</th>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Ohio</th>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Texas</th>\n",
       "      <td>-1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            b  d  e\n",
       "Utah       -1  0  1\n",
       "California -1  0  1\n",
       "Ohio       -1  0  1\n",
       "Texas      -1  0  1"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.sub(series,axis=\"index\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "86e62ecb",
   "metadata": {},
   "source": [
    "#### apply for aggregation function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "ceae3cc6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Beijing</th>\n",
       "      <td>-0.656343</td>\n",
       "      <td>0.792194</td>\n",
       "      <td>0.658560</td>\n",
       "      <td>1.606536</td>\n",
       "      <td>1.366218</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shanghai</th>\n",
       "      <td>1.117954</td>\n",
       "      <td>-0.676052</td>\n",
       "      <td>2.621932</td>\n",
       "      <td>-0.145515</td>\n",
       "      <td>-1.025182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chongqing</th>\n",
       "      <td>-1.749388</td>\n",
       "      <td>0.690545</td>\n",
       "      <td>1.232598</td>\n",
       "      <td>3.061056</td>\n",
       "      <td>-0.815471</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tianjin</th>\n",
       "      <td>-0.613844</td>\n",
       "      <td>0.140733</td>\n",
       "      <td>-0.960486</td>\n",
       "      <td>0.064889</td>\n",
       "      <td>1.031904</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  a         b         c         d         e\n",
       "Beijing   -0.656343  0.792194  0.658560  1.606536  1.366218\n",
       "Shanghai   1.117954 -0.676052  2.621932 -0.145515 -1.025182\n",
       "Chongqing -1.749388  0.690545  1.232598  3.061056 -0.815471\n",
       "Tianjin   -0.613844  0.140733 -0.960486  0.064889  1.031904"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame = pd.DataFrame(np.random.randn(4,5),\n",
    "                    columns=list(\"abcde\"),\n",
    "                    index=['Beijing','Shanghai','Chongqing','Tianjin'])\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "d8204ab6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "a    2.867342\n",
       "b    1.468246\n",
       "c    3.582418\n",
       "d    3.206571\n",
       "e    2.391400\n",
       "dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.apply(lambda x: x.max() - x.min())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "379d3e3f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Beijing      2.262879\n",
       "Shanghai     3.647114\n",
       "Chongqing    4.810443\n",
       "Tianjin      1.992390\n",
       "dtype: float64"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.apply(lambda x: x.max() - x.min(), axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "4e847d89",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>e</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Beijing</th>\n",
       "      <td>-0.66</td>\n",
       "      <td>0.79</td>\n",
       "      <td>0.66</td>\n",
       "      <td>1.61</td>\n",
       "      <td>1.37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Shanghai</th>\n",
       "      <td>1.12</td>\n",
       "      <td>-0.68</td>\n",
       "      <td>2.62</td>\n",
       "      <td>-0.15</td>\n",
       "      <td>-1.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chongqing</th>\n",
       "      <td>-1.75</td>\n",
       "      <td>0.69</td>\n",
       "      <td>1.23</td>\n",
       "      <td>3.06</td>\n",
       "      <td>-0.82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Tianjin</th>\n",
       "      <td>-0.61</td>\n",
       "      <td>0.14</td>\n",
       "      <td>-0.96</td>\n",
       "      <td>0.06</td>\n",
       "      <td>1.03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               a      b      c      d      e\n",
       "Beijing    -0.66   0.79   0.66   1.61   1.37\n",
       "Shanghai    1.12  -0.68   2.62  -0.15  -1.03\n",
       "Chongqing  -1.75   0.69   1.23   3.06  -0.82\n",
       "Tianjin    -0.61   0.14  -0.96   0.06   1.03"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.applymap(lambda x: '%.2f' % x)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "135472b0",
   "metadata": {},
   "source": [
    "#### sort\n",
    "1. sort_index(axis=0, ascending=False)\n",
    "2. sort_values(ascending=False), $NaN$会被放置在最后，视为无穷大\n",
    "3. sort_values(by=[]) by参数指定以哪一列进行排序\n",
    "4. rank(axis,ascedning=False, method=\"average\"|\"min\"|\"max\"|\"first\") method的方法实际上就是在解决相同ranking的过程中\n",
    "5. DataFrame没有规定index不能够重复，但是重复的index会影响sort和rank的结果，如果有多个index，那么iloc或者是loc函数将会返回一个Series，使用.index.is_unique()能够确定是否存在相同的index"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "39c84717",
   "metadata": {},
   "source": [
    "#### summerizing methods\n",
    "1. 所有的聚合函数[Reduction methods]默认都是在Series间进行的，也就是说axis=0，修改这一选项的参数是axis=‘column'或者是asix=1;聚合函数默认不计算$NaN$，控制这一特性的参数是skipna=False;这一类聚合函数有 sum mean max min avg\n",
    "2. 统计学上的聚函数有 var std skew kurt\n",
    "2. 还有accumulation methods 会按照顺序进行输出，比如 cumsum cumprod \n",
    "3. idmax idmin 相当于numpy中的argmax 和 argmin； idmax和argmax的区别在于id直接返回index名称，而arg返回的是位置\n",
    "4. count describe"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21a96d14",
   "metadata": {},
   "source": [
    "#### statistics methods\n",
    "1. corr cov\n",
    "2. 以上两个函数，可以计算两个DataFrame之间的(corrwith covwith)，也可以计算一个DataFrame与一列之间的(worrwith covwith)，也可以计算两列之间的(cov corr)，需要调整参数，<font color= red>即为列索引的两种方式df[\"column\"] | df.column</font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "86e0edd1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Shanghai</th>\n",
       "      <th>Beijing</th>\n",
       "      <th>Chongqing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>2.002898</td>\n",
       "      <td>0.778038</td>\n",
       "      <td>0.427568</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>-1.436270</td>\n",
       "      <td>1.357190</td>\n",
       "      <td>1.397207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <td>0.703072</td>\n",
       "      <td>0.074722</td>\n",
       "      <td>-0.435284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>d</th>\n",
       "      <td>-2.308098</td>\n",
       "      <td>-0.414702</td>\n",
       "      <td>1.212865</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Shanghai   Beijing  Chongqing\n",
       "a  2.002898  0.778038   0.427568\n",
       "b -1.436270  1.357190   1.397207\n",
       "c  0.703072  0.074722  -0.435284\n",
       "d -2.308098 -0.414702   1.212865"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "co_data = pd.DataFrame(np.random.randn(4,3),\n",
    "                       index=list(\"abcd\"),\n",
    "                       columns=[\"Shanghai\",\"Beijing\",\"Chongqing\"])\n",
    "co_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "705b856c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Shanghai</th>\n",
       "      <th>Beijing</th>\n",
       "      <th>Chongqing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Shanghai</th>\n",
       "      <td>3.875511</td>\n",
       "      <td>0.361598</td>\n",
       "      <td>-1.193422</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beijing</th>\n",
       "      <td>0.361598</td>\n",
       "      <td>0.606380</td>\n",
       "      <td>0.175156</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chongqing</th>\n",
       "      <td>-1.193422</td>\n",
       "      <td>0.175156</td>\n",
       "      <td>0.700817</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Shanghai   Beijing  Chongqing\n",
       "Shanghai   3.875511  0.361598  -1.193422\n",
       "Beijing    0.361598  0.606380   0.175156\n",
       "Chongqing -1.193422  0.175156   0.700817"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "co_data.cov()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "5b4816a5",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Shanghai</th>\n",
       "      <th>Beijing</th>\n",
       "      <th>Chongqing</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Shanghai</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.235879</td>\n",
       "      <td>-0.724148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Beijing</th>\n",
       "      <td>0.235879</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.268689</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Chongqing</th>\n",
       "      <td>-0.724148</td>\n",
       "      <td>0.268689</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           Shanghai   Beijing  Chongqing\n",
       "Shanghai   1.000000  0.235879  -0.724148\n",
       "Beijing    0.235879  1.000000   0.268689\n",
       "Chongqing -0.724148  0.268689   1.000000"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "co_data.corr()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "cb729d87",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Shanghai     1.000000\n",
       "Beijing      0.235879\n",
       "Chongqing   -0.724148\n",
       "dtype: float64"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "co_data.corrwith(co_data.Shanghai)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "89a07c55",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.2358787442964535"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "co_data.Beijing.corr(co_data.Shanghai)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "63a094f9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Shanghai     1.0\n",
       "Beijing      1.0\n",
       "Chongqing    1.0\n",
       "dtype: float64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "co_data.corrwith(co_data)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59c20187",
   "metadata": {},
   "source": [
    "#### screening methods\n",
    "1. unique() 返回不重复但是不排名的结果\n",
    "2. value_counts(sort=False) 返回各个结果的计数值，默认不排序\n",
    "3. isin(list) 返回一个boolean数组，指示该位置数据是否在list之中\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2f4fa675",
   "metadata": {},
   "source": [
    "## read write\n",
    "1. from hard desk or from URL\n",
    "2. hard desk - different data formats [csv hdf plain text]\n",
    "3. URL - json html xml"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "30ccbfac",
   "metadata": {},
   "source": [
    "### hard desk\n",
    "1. read_csv read_table - plain text data with , or \\t as dlimiter\n",
    "2. read_excel\n",
    "3. read_hdf\n",
    "4. read_json read_html\n",
    "5. read_pickle\n",
    "6. read_sas read_sql read_stata read_reather"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "acfd6bf3",
   "metadata": {},
   "source": [
    "#### read_csv\n",
    "1. read_csv = read_table(sep=',')\n",
    "2. read_csv 参数<br>\n",
    "header=None 跳过第一行<br>\n",
    "names=[] 重新指定表头行<br>\n",
    "index_col=[] 重新指定索引行，如果是list则按照列表顺序构造层级索引<br>\n",
    "sep= 数据分隔符，可以使用正则表达式<br>\n",
    "skiprows = [] 指定跳过哪几行<br>\n",
    "na_values=[] na_values={} 指定哪些值被视作是NaN，列表作用于全DF，字典每一个key对应一列Series <br>\n",
    "nrows 指定读入的行数<br>\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "820cdb3e",
   "metadata": {},
   "source": [
    "### write\n",
    "1. to_csv(file_name, sep= , na_rep= , index= False, header=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bdc72246",
   "metadata": {},
   "source": [
    "### read data with different delimiters\n",
    "1. module csv\n",
    "2. csv.reader"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "f1ca9c29",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'/Users/collinsliu/jupyter/python_learning_basics'"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import os \n",
    "\n",
    "os.path.abspath('.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "4eb59e21",
   "metadata": {},
   "outputs": [],
   "source": [
    "import csv\n",
    "\n",
    "f = open('datas/example07.csv')\n",
    "reader = csv.reader(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "f4fd765f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['a', 'b', 'c']\n",
      "['1', '2', '3']\n",
      "['1', '2', '3']\n",
      "['tom', 'lucy', 'david']\n"
     ]
    }
   ],
   "source": [
    "for line in reader:\n",
    "    print(line)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cc558d1",
   "metadata": {},
   "source": [
    "### cutomize data reader\n",
    "1. csv.reader(lineterminator=, delimiter=, quotechar=, skipinitialspace=False)参数能够自定义一些内容\n",
    "2. 或者使用reader进行源数据与dict之间的转换"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "d76259ad",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'a': ('1', '1', 'tom'), 'b': ('2', '2', 'lucy'), 'c': ('3', '3', 'david')}"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "with open(\"datas/example07.csv\") as f:\n",
    "    lines = list(csv.reader(f))\n",
    "header, values = lines[0], lines[1:]\n",
    "data_dict = {h:v for h,v in zip(header,zip(*values))}\n",
    "data_dict"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f1066fa",
   "metadata": {},
   "source": [
    "### json\n",
    "1. module json\n",
    "2. json.load() json.dump() 直接将json读成dict，将dict转换成json格式\n",
    "3. pd.read_json() pd.to_json() 直接将json读成DataFrame，将DataFrame转换成json"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75de008a",
   "metadata": {},
   "source": [
    "### html xml\n",
    "1. module lxml beautifulsoup4 html5lib\n",
    "2. pandas built-in function: read_html\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a06bd86f",
   "metadata": {},
   "source": [
    "### binary data format\n",
    "1. pickle - data serialization|deserialization \n",
    "2. read_pickle() to_pickle()\n",
    "3."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "578ae4ac",
   "metadata": {},
   "source": [
    "### Web API Interaction\n",
    "1. module requests\n",
    "2. get(url) 返回一个response对象，每一个response对象都是一个python dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "989c8e04",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<Response [200]>"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import requests\n",
    "\n",
    "url = 'https://api.github.com/repos/pandas-dev/pandas/issues'\n",
    "resp = requests.get(url)\n",
    "\n",
    "resp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "ef7c4321",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'BUG: Timestamp.round overflows'"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = resp.json()\n",
    "data[0]['title']"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f65cdcdd",
   "metadata": {},
   "source": [
    "### SQL \n",
    "1. module sqlite3\n",
    "2. or sqlalchemy + pd.read_sql"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e923c075",
   "metadata": {},
   "source": [
    "## cleaning\n",
    "process of data preparation: loading cleaning transforming rearranging"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "083c5d58",
   "metadata": {},
   "source": [
    "### missing data\n",
    "1. sentinel value - NaN\n",
    "2. all missing data [data holes] will be replaced by NaN\n",
    "3. isnull() notnull() function\n",
    "4. dropna(how='all', axis=0, thresh) \n",
    "5. fillna(value, method='ffill', axis=0, inplace=False, limit)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e38884bd",
   "metadata": {},
   "source": [
    "#### dropna(how, axis, thresh)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "47722ef1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# dropna() for series\n",
    "# dropna(how=\"all\",axis=0) for dataframe, row-wise and drop if has one NaN by default\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "\n",
    "exp_arr = pd.DataFrame(np.random.randn(10,3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "9e268665",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-0.282760</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.472436</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.845040</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.735237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-1.095616</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.512652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-0.875945</td>\n",
       "      <td>-0.155165</td>\n",
       "      <td>0.654327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.468886</td>\n",
       "      <td>-0.059897</td>\n",
       "      <td>1.555549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1.394334</td>\n",
       "      <td>0.368111</td>\n",
       "      <td>-0.055746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-0.804795</td>\n",
       "      <td>-0.572474</td>\n",
       "      <td>-0.383448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.179866</td>\n",
       "      <td>2.011752</td>\n",
       "      <td>-0.371873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1.238025</td>\n",
       "      <td>-0.049248</td>\n",
       "      <td>0.075153</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          0         1         2\n",
       "0 -0.282760       NaN       NaN\n",
       "1  1.472436       NaN       NaN\n",
       "2 -0.845040       NaN  0.735237\n",
       "3 -1.095616       NaN -1.512652\n",
       "4 -0.875945 -0.155165  0.654327\n",
       "5  1.468886 -0.059897  1.555549\n",
       "6  1.394334  0.368111 -0.055746\n",
       "7 -0.804795 -0.572474 -0.383448\n",
       "8  0.179866  2.011752 -0.371873\n",
       "9  1.238025 -0.049248  0.075153"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exp_arr.iloc[:4,1] = np.NaN\n",
    "exp_arr.iloc[:2,2] = np.NaN\n",
    "exp_arr"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "e11a0107",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.845040</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.735237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-1.095616</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-1.512652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-0.875945</td>\n",
       "      <td>-0.155165</td>\n",
       "      <td>0.654327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.468886</td>\n",
       "      <td>-0.059897</td>\n",
       "      <td>1.555549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1.394334</td>\n",
       "      <td>0.368111</td>\n",
       "      <td>-0.055746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-0.804795</td>\n",
       "      <td>-0.572474</td>\n",
       "      <td>-0.383448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.179866</td>\n",
       "      <td>2.011752</td>\n",
       "      <td>-0.371873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1.238025</td>\n",
       "      <td>-0.049248</td>\n",
       "      <td>0.075153</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          0         1         2\n",
       "2 -0.845040       NaN  0.735237\n",
       "3 -1.095616       NaN -1.512652\n",
       "4 -0.875945 -0.155165  0.654327\n",
       "5  1.468886 -0.059897  1.555549\n",
       "6  1.394334  0.368111 -0.055746\n",
       "7 -0.804795 -0.572474 -0.383448\n",
       "8  0.179866  2.011752 -0.371873\n",
       "9  1.238025 -0.049248  0.075153"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exp_arr.dropna(axis=0,thresh=2)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad8f6569",
   "metadata": {},
   "source": [
    "#### fillna(value,method,axis,inplace,limit)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "6b209e54",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>-0.282760</td>\n",
       "      <td>0.257180</td>\n",
       "      <td>0.087068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1.472436</td>\n",
       "      <td>0.257180</td>\n",
       "      <td>0.087068</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>-0.845040</td>\n",
       "      <td>0.257180</td>\n",
       "      <td>0.735237</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>-1.095616</td>\n",
       "      <td>0.257180</td>\n",
       "      <td>-1.512652</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>-0.875945</td>\n",
       "      <td>-0.155165</td>\n",
       "      <td>0.654327</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>1.468886</td>\n",
       "      <td>-0.059897</td>\n",
       "      <td>1.555549</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1.394334</td>\n",
       "      <td>0.368111</td>\n",
       "      <td>-0.055746</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>-0.804795</td>\n",
       "      <td>-0.572474</td>\n",
       "      <td>-0.383448</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>0.179866</td>\n",
       "      <td>2.011752</td>\n",
       "      <td>-0.371873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>1.238025</td>\n",
       "      <td>-0.049248</td>\n",
       "      <td>0.075153</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          0         1         2\n",
       "0 -0.282760  0.257180  0.087068\n",
       "1  1.472436  0.257180  0.087068\n",
       "2 -0.845040  0.257180  0.735237\n",
       "3 -1.095616  0.257180 -1.512652\n",
       "4 -0.875945 -0.155165  0.654327\n",
       "5  1.468886 -0.059897  1.555549\n",
       "6  1.394334  0.368111 -0.055746\n",
       "7 -0.804795 -0.572474 -0.383448\n",
       "8  0.179866  2.011752 -0.371873\n",
       "9  1.238025 -0.049248  0.075153"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exp_arr.fillna(exp_arr.mean(),limit=4)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "aa9fc92e",
   "metadata": {},
   "source": [
    "#### drop_duplicates([columns], keep=\"first\")\n",
    "1. duplicates() 返回一个以行为单位的boolean Series\n",
    "2. drop_duplicates() 将会保留duplicated()操作后返回值为False的行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "92d328e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>k1</th>\n",
       "      <th>k2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>two</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>one</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>two</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>one</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>two</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>two</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    k1  k2\n",
       "0  one   1\n",
       "1  two   1\n",
       "2  one   2\n",
       "3  two   3\n",
       "4  one   3\n",
       "5  two   4\n",
       "6  two   4"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exp_arr2 = pd.DataFrame({\"k1\":[\"one\",\"two\"]*3 + [\"two\"],\n",
    "                         \"k2\":[1,1,2,3,3,4,4]})\n",
    "exp_arr2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "b06baa06",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    False\n",
       "1    False\n",
       "2     True\n",
       "3     True\n",
       "4     True\n",
       "5     True\n",
       "6     True\n",
       "dtype: bool"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exp_arr2.duplicated(['k1'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "2707b272",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>k1</th>\n",
       "      <th>k2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>one</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>two</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    k1  k2\n",
       "4  one   3\n",
       "6  two   4"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "exp_arr2.drop_duplicates(['k1'],keep='last')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "71127392",
   "metadata": {},
   "source": [
    "### data transformation\n",
    "1. map(dict)\n",
    "2. map(function)\n",
    "3. replace(ori,des)\n",
    "4. replace(dic{ori:des})\n",
    "5. rename(index={ori:des},columns={ori,des},inplace=False)\n",
    "6. index.map(function) 同样可以做到改变行名称，但是会在原地直接修改"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "441a85b4",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_arr1 = pd.DataFrame({'food': ['bacon', 'pulled pork', 'bacon',\n",
    "                     'Pastrami', 'corned beef', 'Bacon',\n",
    "                     'pastrami', 'honey ham', 'nova lox'],\n",
    "                     'ounces': [4, 3, 12, 6, 7.5, 8, 3, 5, 6]})\n",
    "meat_to_animal = {'bacon': 'pig',\n",
    "                  'pulled pork': 'pig',\n",
    "                  'pastrami': 'cow',\n",
    "                  'corned beef': 'cow',\n",
    "                  'honey ham': 'pig',\n",
    "                  'nova lox': 'salmon'}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "5c869a3f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>food</th>\n",
       "      <th>ounces</th>\n",
       "      <th>animal</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>bacon</td>\n",
       "      <td>4.0</td>\n",
       "      <td>pig</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>pulled pork</td>\n",
       "      <td>3.0</td>\n",
       "      <td>pig</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bacon</td>\n",
       "      <td>12.0</td>\n",
       "      <td>pig</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Pastrami</td>\n",
       "      <td>6.0</td>\n",
       "      <td>cow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>corned beef</td>\n",
       "      <td>7.5</td>\n",
       "      <td>cow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Bacon</td>\n",
       "      <td>8.0</td>\n",
       "      <td>pig</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>pastrami</td>\n",
       "      <td>3.0</td>\n",
       "      <td>cow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>honey ham</td>\n",
       "      <td>5.0</td>\n",
       "      <td>pig</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>nova lox</td>\n",
       "      <td>6.0</td>\n",
       "      <td>salmon</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          food  ounces  animal\n",
       "0        bacon     4.0     pig\n",
       "1  pulled pork     3.0     pig\n",
       "2        bacon    12.0     pig\n",
       "3     Pastrami     6.0     cow\n",
       "4  corned beef     7.5     cow\n",
       "5        Bacon     8.0     pig\n",
       "6     pastrami     3.0     cow\n",
       "7    honey ham     5.0     pig\n",
       "8     nova lox     6.0  salmon"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_arr1[\"animal\"] = data_arr1[\"food\"].map(lambda x: meat_to_animal[x.lower()])\n",
    "data_arr1"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8499948",
   "metadata": {},
   "source": [
    "### binning\n",
    "1. new pandas object: Categorical\n",
    "2. pandas.cut(value, bins, labels=, right=False, precision=) 默认是左闭右开的区间\n",
    "3. pandas.qcut(value, #bins, labels=) 是按照比例分割成#bin数量的区间\n",
    "2. important attribute in Categorical: codes, categories\n",
    "3. methods for Categorical pandas.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "6fe9e5d9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Apple      250\n",
       "Banan      250\n",
       "Coconut    250\n",
       "Durian     250\n",
       "dtype: int64"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data_arr2 = np.random.randn(1000)\n",
    "label = [\"Apple\",\"Banan\",\"Coconut\",\"Durian\"]\n",
    "cats = pd.qcut(data_arr2, 4, labels=label)\n",
    "pd.value_counts(cats)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "3cb86889",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Apple', 'Durian', 'Coconut', 'Durian', 'Banan', ..., 'Coconut', 'Durian', 'Apple', 'Banan', 'Apple']\n",
       "Length: 1000\n",
       "Categories (4, object): ['Apple' < 'Banan' < 'Coconut' < 'Durian']"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cats"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a52f5137",
   "metadata": {},
   "source": [
    "#### replacing"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "def2a844",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "      <td>1000.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>0.017586</td>\n",
       "      <td>-0.007681</td>\n",
       "      <td>-0.049835</td>\n",
       "      <td>0.006045</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.955416</td>\n",
       "      <td>1.010088</td>\n",
       "      <td>0.986122</td>\n",
       "      <td>1.016413</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-2.784011</td>\n",
       "      <td>-3.179625</td>\n",
       "      <td>-2.658744</td>\n",
       "      <td>-4.029148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>-0.645109</td>\n",
       "      <td>-0.660227</td>\n",
       "      <td>-0.745331</td>\n",
       "      <td>-0.714232</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.010883</td>\n",
       "      <td>-0.009562</td>\n",
       "      <td>-0.038044</td>\n",
       "      <td>0.008224</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>0.678522</td>\n",
       "      <td>0.656478</td>\n",
       "      <td>0.612876</td>\n",
       "      <td>0.698715</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>2.958363</td>\n",
       "      <td>3.854548</td>\n",
       "      <td>2.794963</td>\n",
       "      <td>3.225823</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 0            1            2            3\n",
       "count  1000.000000  1000.000000  1000.000000  1000.000000\n",
       "mean      0.017586    -0.007681    -0.049835     0.006045\n",
       "std       0.955416     1.010088     0.986122     1.016413\n",
       "min      -2.784011    -3.179625    -2.658744    -4.029148\n",
       "25%      -0.645109    -0.660227    -0.745331    -0.714232\n",
       "50%       0.010883    -0.009562    -0.038044     0.008224\n",
       "75%       0.678522     0.656478     0.612876     0.698715\n",
       "max       2.958363     3.854548     2.794963     3.225823"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.DataFrame(np.random.randn(1000,4))\n",
    "data.describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "c5e29d12",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     0    1    2    3\n",
       "0  0.0  0.0  0.0  0.0\n",
       "1  0.0  0.0  0.0  0.0\n",
       "2  0.0  0.0  0.0  0.0\n",
       "3  0.0  0.0  0.0  0.0\n",
       "4  0.0  0.0  0.0  0.0"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[np.abs(data) > 3].fillna(0).head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "bd86fde7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>0.423533</td>\n",
       "      <td>-1.848017</td>\n",
       "      <td>-0.023541</td>\n",
       "      <td>3.148824</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>294</th>\n",
       "      <td>0.240673</td>\n",
       "      <td>-3.179625</td>\n",
       "      <td>-1.014350</td>\n",
       "      <td>-0.119099</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>323</th>\n",
       "      <td>0.943753</td>\n",
       "      <td>3.243925</td>\n",
       "      <td>-1.404744</td>\n",
       "      <td>-1.103809</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>456</th>\n",
       "      <td>0.433519</td>\n",
       "      <td>0.598823</td>\n",
       "      <td>-0.845604</td>\n",
       "      <td>3.225823</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>476</th>\n",
       "      <td>0.385804</td>\n",
       "      <td>0.046352</td>\n",
       "      <td>-0.559230</td>\n",
       "      <td>-4.029148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>689</th>\n",
       "      <td>-1.225403</td>\n",
       "      <td>3.854548</td>\n",
       "      <td>-1.372414</td>\n",
       "      <td>-0.035020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>740</th>\n",
       "      <td>1.057911</td>\n",
       "      <td>-1.387283</td>\n",
       "      <td>-0.516550</td>\n",
       "      <td>-3.030365</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>782</th>\n",
       "      <td>-0.131432</td>\n",
       "      <td>0.374464</td>\n",
       "      <td>-0.978603</td>\n",
       "      <td>-3.250297</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            0         1         2         3\n",
       "27   0.423533 -1.848017 -0.023541  3.148824\n",
       "294  0.240673 -3.179625 -1.014350 -0.119099\n",
       "323  0.943753  3.243925 -1.404744 -1.103809\n",
       "456  0.433519  0.598823 -0.845604  3.225823\n",
       "476  0.385804  0.046352 -0.559230 -4.029148\n",
       "689 -1.225403  3.854548 -1.372414 -0.035020\n",
       "740  1.057911 -1.387283 -0.516550 -3.030365\n",
       "782 -0.131432  0.374464 -0.978603 -3.250297"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data[(np.abs(data)>3).any(1)].fillna(0)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e0a2bac7",
   "metadata": {},
   "source": [
    "### generate one-hot\n",
    "1. get_dummies(series, prefix=)\n",
    "2. series.add_prefix()\n",
    "3. datafame.columns.get_indexer()\n",
    "4. combine binning with one-hot: pandas.get_dummins(pd.cut(value, bin, labels, right=False))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "421cad6b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([0.12696983, 0.96671784, 0.26047601, 0.89723652, 0.37674972,\n",
       "       0.33622174, 0.45137647, 0.84025508, 0.12310214, 0.5430262 ])"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.random.seed(123456)\n",
    "values = np.random.rand(10)\n",
    "values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "28570dff",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Apple</th>\n",
       "      <th>Banana</th>\n",
       "      <th>Cherry</th>\n",
       "      <th>Durian</th>\n",
       "      <th>Elderwood</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Apple  Banana  Cherry  Durian  Elderwood\n",
       "0      1       0       0       0          0\n",
       "1      0       0       0       0          1\n",
       "2      0       1       0       0          0\n",
       "3      0       0       0       0          1\n",
       "4      0       1       0       0          0\n",
       "5      0       1       0       0          0\n",
       "6      0       0       1       0          0\n",
       "7      0       0       0       0          1\n",
       "8      1       0       0       0          0\n",
       "9      0       0       1       0          0"
      ]
     },
     "execution_count": 44,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bins = [0, 0.2, 0.4, 0.6, 0.8, 1]\n",
    "names = [\"Apple\", \"Banana\", \"Cherry\", \"Durian\", \"Elderwood\"]\n",
    "pd.get_dummies(pd.cut(values, bins, labels=names))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8418cfd3",
   "metadata": {},
   "source": [
    "### string munging\n",
    "1. python build-in functions: <font color=red>split('delimiter'), strip()</font>\n",
    "2. lstrip rstrip ljust rjust count\n",
    "3. startwith endwith\n",
    "4. upper lower\n",
    "2. pythonic string concatenation: <font color=red>+ 或 \"delimiter\".join(list)</font>\n",
    "3. python substring location methods: <font color=red>index(char)[raise exception], find(char)</font>\n",
    "4. replace"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "21faf7fd",
   "metadata": {},
   "source": [
    "### re\n",
    "module re\n",
    "1. pattern matching\n",
    "2. substitution\n",
    "3. splitting"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "52ef3a64",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['foo', 'bar', 'tar', 'qua']"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import re\n",
    "\n",
    "text = \"foo    bar\\t  tar  \\t qua\"\n",
    "\n",
    "regex = re.compile('\\s+')\n",
    "regex.split(text)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "16d89dbd",
   "metadata": {},
   "source": [
    "### vectorize string"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f9bced12",
   "metadata": {},
   "source": [
    "## Wrangling\n",
    "### hierarchical index\n",
    "1. unstack() stack()\n",
    "2. hierarchical index could happen in both index and column\n",
    "3. swaplevel(level_a, level_b, axis=0) sort_index(level=, axis=0)\n",
    "4. change is not in-place \n",
    "5. note: sort_index is much faster if sort happens in the outer-most level"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "693e3f7a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>color</th>\n",
       "      <th>Green</th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "state      Ohio     Colorado\n",
       "color     Green Red    Green\n",
       "key1 key2                   \n",
       "a    1        0   1        2\n",
       "     2        3   4        5\n",
       "b    1        6   7        8\n",
       "     2        9  10       11"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame = pd.DataFrame(np.arange(12).reshape((4, 3)),\n",
    "        index=[['a', 'a', 'b', 'b'], [1, 2, 1, 2]],\n",
    "        columns=[['Ohio', 'Ohio', 'Colorado'],\n",
    "        ['Green', 'Red', 'Green']])\n",
    "frame.columns.names = [\"state\", \"color\"]\n",
    "frame.index.names = [\"key1\",\"key2\"]\n",
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "ab9276ec",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>color</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Green</th>\n",
       "      <th>Red</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th>Colorado</th>\n",
       "      <th>Ohio</th>\n",
       "      <th>Ohio</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>1</th>\n",
       "      <td>8</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>11</td>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "color        Green       Red\n",
       "state     Colorado Ohio Ohio\n",
       "key1 key2                   \n",
       "a    1           2    0    1\n",
       "     2           5    3    4\n",
       "b    1           8    6    7\n",
       "     2          11    9   10"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.swaplevel(0,1,axis=1).sort_index(level=0,axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "bd5f3f14",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>state</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>color</th>\n",
       "      <th>Green</th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>1</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>1</th>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "state      Ohio     Colorado\n",
       "color     Green Red    Green\n",
       "key1 key2                   \n",
       "a    1        0   1        2\n",
       "     2        3   4        5\n",
       "b    1        6   7        8\n",
       "     2        9  10       11"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "4759c7bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>color</th>\n",
       "      <th>Green</th>\n",
       "      <th>Red</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th>key2</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">a</th>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">b</th>\n",
       "      <th>1</th>\n",
       "      <td>14</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>20</td>\n",
       "      <td>10</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "color      Green  Red\n",
       "key1 key2            \n",
       "a    1         2    1\n",
       "     2         8    4\n",
       "b    1        14    7\n",
       "     2        20   10"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.groupby(level=1,axis=1).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2c89cf42",
   "metadata": {},
   "source": [
    "### groupby transform\n",
    "\n",
    "<h6>groupby(by=) 返回一个分组，适合聚合函数</h6>\n",
    "<hr>\n",
    "\n",
    "2. groupby().get_group() 或 groupby()[index] 能够获取分组\n",
    "3. groupby().agg(aggregation_function)能够对分组进行聚合函数处理\n",
    "4. <b><font color=red>groupby().agg({column:aggregation_function})</font></b> 是通用的聚合函数处理方法\n",
    "5. reset_index() 对聚合结果进行重新添加index的操作\n",
    "6. rename(columns={ori_name:new_name}) 对聚合结果进行column重命名操作\n",
    "\n",
    "<h6>transform(aggregation_function) 类似于开窗函数，对每一行的值都返回聚合函数的结果</h6>\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "d07a7e0c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th>state</th>\n",
       "      <th colspan=\"2\" halign=\"left\">Ohio</th>\n",
       "      <th>Colorado</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>color</th>\n",
       "      <th>Green</th>\n",
       "      <th>Red</th>\n",
       "      <th>Green</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>key1</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>a</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>b</th>\n",
       "      <td>15</td>\n",
       "      <td>17</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "state  Ohio     Colorado\n",
       "color Green Red    Green\n",
       "key1                    \n",
       "a         3   5        7\n",
       "b        15  17       19"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame.groupby(['key1']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de5e114d",
   "metadata": {},
   "source": [
    "### index\n",
    "1. set_index(drop=True) 将columns转化为index\n",
    "2. reset_index() 将index重新转化为column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "c8aac34e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "      <td>one</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>one</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>two</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>two</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>two</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>two</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   a  b    c  d\n",
       "0  0  7  one  0\n",
       "1  1  6  one  1\n",
       "2  2  5  one  2\n",
       "3  3  4  two  0\n",
       "4  4  3  two  1\n",
       "5  5  2  two  2\n",
       "6  6  1  two  3"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame_2 = pd.DataFrame({'a': range(7), 'b': range(7, 0, -1),\n",
    "        'c': ['one', 'one', 'one', 'two', 'two',\n",
    "        'two', 'two'],\n",
    "        'd': [0, 1, 2, 0, 1, 2, 3]})\n",
    "frame_2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "4bd01549",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">one</th>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">two</th>\n",
       "      <th>0</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       a  b\n",
       "c   d      \n",
       "one 0  0  7\n",
       "    1  1  6\n",
       "    2  2  5\n",
       "two 0  3  4\n",
       "    1  4  3\n",
       "    2  5  2\n",
       "    3  6  1"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame_2new = frame_2.set_index(['c','d'])\n",
    "frame_2new"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "dd5711f2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>c</th>\n",
       "      <th>d</th>\n",
       "      <th>a</th>\n",
       "      <th>b</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>one</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>one</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>two</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>two</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>two</td>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>two</td>\n",
       "      <td>3</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     c  d  a  b\n",
       "0  one  0  0  7\n",
       "1  one  1  1  6\n",
       "2  one  2  2  5\n",
       "3  two  0  3  4\n",
       "4  two  1  4  3\n",
       "5  two  2  5  2\n",
       "6  two  3  6  1"
      ]
     },
     "execution_count": 60,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "frame_2rev = frame_2new.reset_index()\n",
    "frame_2rev"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20c91c48",
   "metadata": {},
   "source": [
    "### combinations\n",
    "1. merge() 行层面连接 - 和sql的join是一个意思\n",
    "2. concat() 可以指定行或者列连接\n",
    "3. combine_first() 增加了功能：重复的拼接，缺失的补齐"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1dcc2d9d",
   "metadata": {},
   "source": [
    "#### merge\n",
    "<h6> merge(dataframe1, dataframe2, left_on, right_on, how, suffixes=(), indicator=False, left_index=, right_index)</h6>\n",
    "<hr></hr>\n",
    "\n",
    "1. on left_on right_on 指定合并时两表中指定相同的列\n",
    "2. how 指定合并式的方式 outer inner left right\n",
    "3. suffixes 用于区分合并后指定相同的列来自于左表还是右表\n",
    "4. indicator显示数据来自于哪张表，默认为False\n",
    "5. left_index 与 right_index 指定index作为merge key，而非某一个column\n",
    "\n",
    "1. 实际上，如果依照index进行join操作，可以直接使用<b><font color=red>left.join(right1,right2,..., on, how)</font></b>进行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "id": "9e8fa7ba",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>key1</th>\n",
       "      <th>key2_left</th>\n",
       "      <th>lval</th>\n",
       "      <th>key2_right</th>\n",
       "      <th>rval</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>foo</td>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>one</td>\n",
       "      <td>4</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>foo</td>\n",
       "      <td>one</td>\n",
       "      <td>1</td>\n",
       "      <td>one</td>\n",
       "      <td>5</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>foo</td>\n",
       "      <td>two</td>\n",
       "      <td>2</td>\n",
       "      <td>one</td>\n",
       "      <td>4</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>foo</td>\n",
       "      <td>two</td>\n",
       "      <td>2</td>\n",
       "      <td>one</td>\n",
       "      <td>5</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>bar</td>\n",
       "      <td>one</td>\n",
       "      <td>3</td>\n",
       "      <td>one</td>\n",
       "      <td>6</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>bar</td>\n",
       "      <td>one</td>\n",
       "      <td>3</td>\n",
       "      <td>two</td>\n",
       "      <td>7</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  key1 key2_left  lval key2_right  rval _merge\n",
       "0  foo       one     1        one     4   both\n",
       "1  foo       one     1        one     5   both\n",
       "2  foo       two     2        one     4   both\n",
       "3  foo       two     2        one     5   both\n",
       "4  bar       one     3        one     6   both\n",
       "5  bar       one     3        two     7   both"
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "left = pd.DataFrame({'key1': ['foo', 'foo', 'bar'],\n",
    "                     'key2': ['one', 'two', 'one'],\n",
    "                     'lval': [1, 2, 3]})    \n",
    "right = pd.DataFrame({'key1': ['foo', 'foo', 'bar', 'bar'],\n",
    "                      'key2': ['one', 'one', 'one', 'two'],\n",
    "                      'rval': [4, 5, 6, 7]})\n",
    "pd.merge(left, right, \n",
    "         on='key1', \n",
    "         how='outer',\n",
    "         suffixes=('_left',\"_right\"), \n",
    "         indicator=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7b0cd110",
   "metadata": {},
   "source": [
    "#### concat\n",
    "1. np.concatenate([array1,array2,...],axis=0)\n",
    "2. pd.concat([series1,series2,...],axis=0,join=\"inner\"，join_axesjoin_axes=[[index1,index2,...]]) join关键词解决overlap问题"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f1e62882",
   "metadata": {},
   "source": [
    "## processing\n",
    "1. split\n",
    "2. apply\n",
    "3. combination\n",
    "\n",
    "### groupby\n",
    "\n",
    "1. <font color=red size=4.5><b>DataFrame.groupby( [ function,mapping{dict,Series} ], level=0, axis=0, as_index=False, group_keys=False )[ column ]</b></font>\n",
    "2. groupby()函数将会返回一个grouped对象，dict(list(grouped))可以将其转换为字典形式\n",
    "3. 聚合函数可以直接作用在grouped对象上，自定义聚合函数同样也可以<font color=red size=4.5> <b>grouped.agg((name1,function1),(name2,function2)...)</b></font>对所有列使用，或者对每个列规定使用的聚合函数<font color=red size=4.5> <b>grouped.agg( {column1:(name1,function1),(name2,function2)...,column2:...} )</b></font>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8c3bff9d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "596e177d",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "python3.6",
   "language": "python",
   "name": "python3.6"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "277.594px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
